package kr.pe.mybjj;

import java.sql.Connection;
//import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;

import javax.naming.Context;
import javax.naming.InitialContext;
import javax.naming.NamingException;
import javax.sql.DataSource;

public class QnADAO {

	// Connection Pool 사용.
	DataSource dataFactory;	
	public QnADAO() {
		try {
			Context ctx = new InitialContext();
			dataFactory = (DataSource)ctx.lookup("java:comp/env/jdbc/Oracle11g");
		} catch (NamingException e) {
			e.printStackTrace();
		}
	}
	
	public ArrayList<QnADTO> select()
	{
		ArrayList<QnADTO> list = new ArrayList<QnADTO>();
		
		Connection con = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		
		try {
			con = dataFactory.getConnection();
			String sql = "select no, title, content, username, password, to_char(createdate, 'YYYY-MM-DD HH24:MI') as createdate from T_BJJ_QNA  ORDER BY no desc ";
			pstmt = con.prepareStatement(sql);
			rs = pstmt.executeQuery();
			while(rs.next())
			{
				int no = rs.getInt("no");
				String title = rs.getString("title");
				String content = rs.getString("content");
				String username = rs.getString("username");
				String password = rs.getString("password");
				String createdate = rs.getString("createdate");
				
				QnADTO dto = new QnADTO(no, title, content, username, password, createdate);
				list.add(dto);
			}
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		finally
		{
			try {
				if(rs!=null)rs.close();
				if(pstmt!=null)pstmt.close();
				if(con!=null)con.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}			
		}
		
		return list;
	}
	
	public int insert(QnADTO data)
	{
		Connection con = null;
		PreparedStatement pstmt = null;
		int n = 0;
		
		try {
			con = dataFactory.getConnection();
			//String sql = "insert into T_BJJ_QNA ( title, content, username, password ) values ('" + data.gettitle() + "', '" + data.getcontent() + "', " + data.getusername() + ", '" + data.getpassword() + "'" )";
			String sql = "insert into T_BJJ_QNA ( no, title, content, username, password ) values ( SEQ_QNA.NEXTVAL, ?, ?, ?, ? )";
			//System.out.print(sql);
			pstmt = con.prepareStatement(sql);
			pstmt.setString(1, data.gettitle());
			pstmt.setString(2, data.getcontent());
			pstmt.setString(3, data.getuserName());
			pstmt.setString(4, data.getpassword());
			
			n = pstmt.executeUpdate();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		finally
		{
			try {
				if(pstmt!=null)pstmt.close();
				if(con!=null)con.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}			
		}
		
		return n;
	}
	
	public int update(QnADTO data)
	{
		Connection con = null;
		PreparedStatement pstmt = null;
		int n = 0;
		
		try {
			con = dataFactory.getConnection();
			String sql = "update T_BJJ_QNA set content = ? , username = ?, password = ? where title = ? ";
			pstmt = con.prepareStatement(sql);
			pstmt.setString(1, data.gettitle());
			pstmt.setString(2, data.getcontent());
			pstmt.setString(3, data.getuserName());
			pstmt.setString(4, data.getpassword());
			
			n = pstmt.executeUpdate();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		finally
		{
			try {
				if(pstmt!=null)pstmt.close();
				if(con!=null)con.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}			
		}
		
		return n;
	}
	
	public int delete(String title)
	{
		Connection con = null;
		PreparedStatement pstmt = null;
		int n = 0;
		
		try {
			con = dataFactory.getConnection();
			String sql = "delete from T_BJJ_QNA where title = ? ";
			pstmt = con.prepareStatement(sql);
			pstmt.setString(1, title);
			
			n = pstmt.executeUpdate();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		finally
		{
			try {
				if(pstmt!=null)pstmt.close();
				if(con!=null)con.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}			
		}
		
		return n;
	}
	
	public ArrayList<QnADTO> search(String searchType, String searchValue)
	{
		ArrayList<QnADTO> list = new ArrayList<QnADTO>();
		
		Connection con = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		
		try {
			con = dataFactory.getConnection();
			String sql = "select no, title, content, username, password, createdate from T_BJJ_QNA where " + searchType + " = ? ";
			pstmt = con.prepareStatement(sql);
			
			switch(searchType)
			{
			case "title":
			case "content":
			case "password":
			case "username":
				pstmt.setString(1, searchValue);
				break;
			case "no":
				pstmt.setInt(1, Integer.parseInt(searchValue));
				break;
			}

			System.out.print(pstmt);
			rs = pstmt.executeQuery();
			
			while(rs.next())
			{
				int no = rs.getInt("no");
				String title = rs.getString("title");
				String content = rs.getString("content");
				String username = rs.getString("username");
				String password = rs.getString("password");
				String createdate = rs.getString("createdate");
				
				QnADTO dto = new QnADTO(no, title, content, username, password, createdate);
				list.add(dto);
			}
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		finally
		{
			try {
				if(rs!=null)rs.close();
				if(pstmt!=null)pstmt.close();
				if(con!=null)con.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}			
		}
		return list;
	}

}
